from flask import Flask, request, render_template, redirect, url_for,flash
import sqlite3 # 导入sqlit3库
import os
import time
# 连接到一个数据库名为 data 的数据库,如果存在则直接连接,如果不存在则创建
data_base=sqlite3.connect('data', check_same_thread=False)

# 设置数据库光标,你之后所有对数据库进行的操作都是通过光标来执行的
cursor=data_base.cursor()
# 创建一个表名为 accounts 的数据库表,如果这个表不存在的话
# 后面括号里面的内容为这个表的属性,属性与属性之间用  ,  隔开,属性名与属性类型之间用 空格  隔开,如果不写类型的话,默认       为text类型
cursor.execute('create table if not exists accounts(name text,mail text,pwd text)')
cursor.execute('create table if not exists comments(says text)')
# 数据库的提交,对数据进行增删改后都需要进行数据库的提交
data_base.commit()



app = Flask(__name__)

@app.route('/')
@app.route('/home1')
def home1():
    return render_template("index1.html")

@app.route('/index1')
def index1():
	if request.method == 'GET':
		return render_template("index.html")
	else:
		name=request.form.get('name')
		

@app.route('/index',methods=['GET'])
def index():
	if request.method == 'GET':
		return render_template("index.html")
	else:
		name=request.form.get('name')


@app.route('/register',methods=['GET', 'POST'])
def register():
	if request.method == 'GET':
		return render_template("register.html")
	else:
		name=request.form.get('name')
		mail=request.form.get('mail')
		pwd=request.form.get('pwd')
		# print('name:%s'%name,"\nmail:%s"%mail,"\npwd:%s"%pwd)
		cursor.execute('insert into accounts (name,mail,pwd) VALUES (\"%s\",\"%s\",\"%s\")'%(name,mail,pwd))
		data_base.commit()
		cursor.execute('select * from accounts')
		result=cursor.fetchall()
		print(result)
		return redirect(url_for('index'))

@app.route('/home',methods=['GET', 'POST'])
def home():
	if request.method == 'GET':
		return redirect(url_for('index'))
	else:
		name=request.form.get('name')
		pwd=request.form.get('pwd')
		cursor.execute('select * from accounts WHERE name=\"%s\"'%name)
		result=cursor.fetchall()
		# print(result[0][2])##密码
		if pwd==result[0][2]:
			return render_template("home.html",username=name)
		else:
			return redirect(url_for('index2'))

# 文件上传的地址
saved_path = 'static/files'
# In[3]:


data_base=sqlite3.connect('reocords', check_same_thread=False)
cursor=data_base.cursor()
cursor.execute('create table if not exists uploads(info text)')
data_base.commit()


# In[4]:


def getrecords():
    cursor.execute('select * from uploads')
    results=cursor.fetchall()
    records=[]
    for r in results:
        records.append(eval(r[0]))
    return records


# In[5]:


@app.route('/index2', methods=['GET', 'POST'])
def index2():
    if request.method == 'GET':
        records=getrecords()
        # print(records)
        return render_template('index2.html',records=records[::-1])

    else:#post方法
        scon = request.form.get('scon')
        target = []
        # 搜索云端是否有该文件，罗列所有相关的
        records=getrecords()
        for record in records:
            # print(record['filename'])
            if scon in record['filename']:
                target.append(record)
        if target==[]:#没有找到相关文件
            flash('没有找到相关文件！')
            return render_template('index2.html',records=records[::-1])
        else:
            return render_template('index2.html',records=target[::-1])


# In[6]:


@app.route('/upload', methods=['GET', 'POST'])
def upload_file():
    if request.method == 'POST':
        file = request.files['file']
        if file:
            filename = file.filename.rsplit('.', 1)[0]
            filetype = file.filename.rsplit('.', 1)[1]
            filename = filename.replace(' ','') # 将文件名中的空格去除
            filename = filename.replace('.','-') # 去除文件中的.
            filename = filename+'.'+filetype
            # 解决命名冲突的问题
            records=getrecords()
            for record in records:
                if filename==record['filename']:
                    flash('该文件已经存在！')
                    return render_template('upload.html')
            # ----------
            file.save(os.path.join(saved_path, filename))
            date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            record = {"filename": filename, "date": date}
            # print(record)
            # 更新本地记录
            cursor.execute('insert into uploads (info) VALUES (\"%s\")'%(record))
            data_base.commit()
            return redirect(url_for('index2'))
    return render_template('upload.html')


# In[7]:


@app.route("/<filename>", methods=['GET'])
def classify(filename):
    path = "../"+saved_path+"/"+filename
    filetype=filename.split(".",-1)[-1]
    if filetype == 'pdf':
        return render_template('pdf.html',path=path)
    elif filetype in {'png','jpg','jpeg','tiff'}:
        return render_template('img.html',path=path)
    elif filetype == 'mp4':
        return render_template('vedio.html',path=path)
    elif filetype == {'mp3','m4a'}:
        return render_template('audio.html',path=path)
    else:
        return render_template('player.html',path=path)



@app.route('/comment',methods=['GET', 'POST'])
def comment():
	if request.method == 'GET':
		cursor.execute('select * from comments')
		records=cursor.fetchall()
		
		says=[]
		for r in records:
			says.append(eval(r[0]))
		print(says)
		return render_template("comment.html", says=says)
	else:
		username = request.form.get('username')
		title = request.form.get('title')
		content = request.form.get('content')
		date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
		say={"title": title, "content": content, "username": username, "date": date}
		# 添加留言记录
		cursor.execute('insert into comments (says) VALUES (\"%s\")'%(say))
		data_base.commit()
		return redirect(url_for('comment'))


if __name__=='__main__':
    app.run(debug=True)#host='0.0.0.0',